﻿

using NPOI;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;

namespace ServiceWeb.Excel
{
    public class NpoiExcelHelper
    {
        public static bool ExcelProjectExport(DataTable dt, string filePath, string filename, string tittle, DataTable dtxls)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            var sheet = workbook.CreateSheet(tittle);
            var style = workbook.CreateCellStyle();
            IFont f = workbook.CreateFont();
            f.Boldweight = (short)FontBoldWeight.Bold;
            style.Alignment = HorizontalAlignment.CenterSelection;//设置居中 方法2
            style.SetFont(f);



            sheet.DefaultColumnWidth = 20;
            sheet.ForceFormulaRecalculation = true;
            IRow rowhead;
            //ICell  cellhead;
            for (int irow = 0; irow < 2; irow++)
            {
                rowhead = sheet.CreateRow(irow);
                for (int col = 0; col < dt.Columns.Count; col++)
                {
                    rowhead.CreateCell(col);
                }
            }




            CellRangeAddress region = new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1);
            sheet.AddMergedRegion(region);
            sheet.GetRow(0).GetCell(0).CellStyle = style;
            sheet.GetRow(0).GetCell(0).SetCellValue(tittle);

            region = new CellRangeAddress(1, 1, 0, dt.Columns.Count - 1);
            sheet.AddMergedRegion(region);
            sheet.GetRow(1).GetCell(0).SetCellValue(tittle);
            sheet.GetRow(1).GetCell(0).SetCellValue("导出日期：" + DateTime.Now.ToString("yyyy年MM月dd日"));

            var rowIndex = 2;
            AddHeader(dt, rowIndex, workbook, sheet);


            ICell cell;
            IRow row;


            for (int i = 0; i < dt.Rows.Count; i++)
            {
                row = sheet.CreateRow(i + 3);

                for (int ii = 0; ii < dt.Columns.Count; ii++)
                {



                    cell = row.CreateCell(ii);
                    cell.SetCellValue(dt.Rows[i][ii].ToString());


                }

            }
            //Excel的路径及名称
            string excelPath = filePath + filename;
            FileStream fileStream = new FileStream(excelPath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fileStream);
            fileStream.Close();



            // workbook.Save(filePath);
            // yn = true;
            return true;
            //}
            //catch (Exception e)
            //{
            //    return yn;
            //    // throw e;
            //}

        }
        public static bool ExportDataToExcel(DataTable dt, string Path, string filename, string title, string rowsonecontent)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            //   ISheet sheet = workbook.CreateSheet("sheet");

            var sheet = workbook.CreateSheet(title);
            sheet.DefaultColumnWidth = 20;
            sheet.ForceFormulaRecalculation = true;
            IRow rowhead;
            //ICell  cellhead;
            for (int irow = 0; irow < 2; irow++)
            {
                rowhead = sheet.CreateRow(irow);
                for (int col = 0; col < dt.Columns.Count; col++)
                {
                    rowhead.CreateCell(col);
                }
            }

            var style = workbook.CreateCellStyle();
            IFont f = workbook.CreateFont();
            f.Boldweight = (short)FontBoldWeight.Bold;
            style.Alignment = HorizontalAlignment.CenterSelection;//设置居中 方法2
            style.SetFont(f);

            //第一个参数：从第几行开始合并
            //    第二个参数：到第几行结束合并
            //    第三个参数：从第几列开始合并
            //    第四个参数：到第几列结束合并


            CellRangeAddress region = new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1);
            sheet.AddMergedRegion(region);
            sheet.GetRow(0).GetCell(0).SetCellValue(title);
            sheet.GetRow(0).GetCell(0).CellStyle = style;

            region = new CellRangeAddress(1, 1, 0, dt.Columns.Count - 1);
            sheet.AddMergedRegion(region);
            sheet.GetRow(1).GetCell(0).SetCellValue("导出日期：" + DateTime.Now.ToString("yyyy年MM月dd日"));
            //  sheet.GetRow(1).GetCell(0).CellStyle = style;


            var rowIndex = 2;
            AddHeader(dt, rowIndex, workbook, sheet);

            ICell cell;
            IRow row;
            //单元格边框
            var cellStyle = workbook.CreateCellStyle();
            //cellStyle.BorderBottom = BorderStyle.Thin;
            //cellStyle.BorderLeft = BorderStyle.Thin;
            //cellStyle.BorderRight = BorderStyle.Thin;
            //cellStyle.BorderTop = BorderStyle.Thin;

            var cellStylelink = cellStyle;
            IFont hlink_font = workbook.CreateFont();
            hlink_font.Underline = FontUnderlineType.Single;
            hlink_font.Color = HSSFColor.Blue.Index;
            cellStylelink.SetFont(hlink_font);


            for (int r = 0; r < dt.Rows.Count; r++)
            {
                //cellStyle.SetFont(null);
                row = sheet.CreateRow(r + 3);
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    cell = row.CreateCell(c);
                    // cellStyle.SetFont(null);

                    if (dt.Rows[r][c].ToString().Contains("http"))
                    {
                        cell.SetCellValue("点击查看");
                        XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.Url);
                        link.Address = dt.Rows[r][c].ToString();
                        cell.Hyperlink = link;

                        cell.CellStyle = cellStylelink;
                    }
                    else
                    {
                        cell.SetCellValue(dt.Rows[r][c].ToString());
                        // cell.CellStyle = cellStyle;

                    }



                }
            }
            FileStream fileStream = null;
            string excelPath = ""; ;



            //运行环境 路径
            excelPath = Environment.CurrentDirectory + "/" + Path + filename;
            //Excel的路径及名称
            fileStream = new FileStream(excelPath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fileStream);
            fileStream.Close();
            return true;
        }

        public static bool ExportDataToExcel(ArrayList HeadList, DataTable dt, string Path, string filename, string title, string rowsonecontent)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            //   ISheet sheet = workbook.CreateSheet("sheet");

            var sheet = workbook.CreateSheet(title);
            sheet.DefaultColumnWidth = 20;
            sheet.ForceFormulaRecalculation = true;
            IRow rowhead;
            //ICell  cellhead;
            for (int irow = 0; irow < 2; irow++)
            {
                rowhead = sheet.CreateRow(irow);
                for (int col = 0; col < dt.Columns.Count; col++)
                {
                    rowhead.CreateCell(col);
                }
            }

            var style = workbook.CreateCellStyle();
            IFont f = workbook.CreateFont();
            f.Boldweight = (short)FontBoldWeight.Bold;
            style.Alignment = HorizontalAlignment.CenterSelection;//设置居中 方法2
            style.SetFont(f);

            //第一个参数：从第几行开始合并
            //    第二个参数：到第几行结束合并
            //    第三个参数：从第几列开始合并
            //    第四个参数：到第几列结束合并


            CellRangeAddress region = new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1);
            sheet.AddMergedRegion(region);
            sheet.GetRow(0).GetCell(0).SetCellValue(title);
            sheet.GetRow(0).GetCell(0).CellStyle = style;

            region = new CellRangeAddress(1, 1, 0, dt.Columns.Count - 1);
            sheet.AddMergedRegion(region);
            sheet.GetRow(1).GetCell(0).SetCellValue("导出日期：" + DateTime.Now.ToString("yyyy年MM月dd日"));
            //  sheet.GetRow(1).GetCell(0).CellStyle = style;


            var rowIndex = 2;
            AddHeader(HeadList, rowIndex, workbook, sheet);

            ICell cell;
            IRow row;
            //单元格边框
            var cellStyle = workbook.CreateCellStyle();
            //cellStyle.BorderBottom = BorderStyle.Thin;
            //cellStyle.BorderLeft = BorderStyle.Thin;
            //cellStyle.BorderRight = BorderStyle.Thin;
            //cellStyle.BorderTop = BorderStyle.Thin;

            var cellStylelink = cellStyle;
            IFont hlink_font = workbook.CreateFont();
            hlink_font.Underline = FontUnderlineType.Single;
            hlink_font.Color = HSSFColor.Blue.Index;
            cellStylelink.SetFont(hlink_font);


            for (int r = 0; r < dt.Rows.Count; r++)
            {
                //cellStyle.SetFont(null);
                row = sheet.CreateRow(r + 3);
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    cell = row.CreateCell(c);
                    // cellStyle.SetFont(null);

                    if (dt.Rows[r][c].ToString().Contains("http"))
                    {
                        cell.SetCellValue("点击查看");
                        XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.Url);
                        link.Address = dt.Rows[r][c].ToString();
                        cell.Hyperlink = link;

                        cell.CellStyle = cellStylelink;
                    }
                    else
                    {
                        cell.SetCellValue(dt.Rows[r][c].ToString());
                        // cell.CellStyle = cellStyle;

                    }



                }
            }



            //Excel的路径及名称
            string excelPath = Path + filename;
            FileStream fileStream = new FileStream(excelPath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fileStream);
            fileStream.Close();










            return true;
        }



        public static bool WriteExcel_FailExcel(ArrayList HeadList, DataTable dt, string Path, string filename, string title, string rowsonecontent)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            //   ISheet sheet = workbook.CreateSheet("sheet");

            var sheet = workbook.CreateSheet(title);
            sheet.DefaultColumnWidth = 20;
            sheet.ForceFormulaRecalculation = true;
            IRow rowhead;


            var style = workbook.CreateCellStyle();
            IFont f = workbook.CreateFont();
            f.Boldweight = (short)FontBoldWeight.Bold;
            style.Alignment = HorizontalAlignment.CenterSelection;//设置居中 方法2
            style.SetFont(f);

            //第一个参数：从第几行开始合并
            //    第二个参数：到第几行结束合并
            //    第三个参数：从第几列开始合并
            //    第四个参数：到第几列结束合并





            var rowIndex = 0;
            AddHeader(HeadList, rowIndex, workbook, sheet);

            ICell cell;
            IRow row;
            //单元格边框
            var cellStyle = workbook.CreateCellStyle();
            var cellStylelink = cellStyle;
            IFont hlink_font = workbook.CreateFont();
            hlink_font.Underline = FontUnderlineType.Single;
            hlink_font.Color = HSSFColor.Blue.Index;
            cellStylelink.SetFont(hlink_font);


            for (int r = 0; r < dt.Rows.Count; r++)
            {
                //cellStyle.SetFont(null);
                row = sheet.CreateRow(r + 1);
                for (int c = 0; c < dt.Columns.Count; c++)
                {
                    cell = row.CreateCell(c);
                    // cellStyle.SetFont(null);

                    if (dt.Rows[r][c].ToString().Contains("http"))
                    {
                        cell.SetCellValue("点击查看");
                        XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.Url);
                        link.Address = dt.Rows[r][c].ToString();
                        cell.Hyperlink = link;

                        cell.CellStyle = cellStylelink;
                    }
                    else
                    {
                        cell.SetCellValue(dt.Rows[r][c].ToString());
                        // cell.CellStyle = cellStyle;

                    }



                }
            }



            //Excel的路径及名称
            string excelPath = Path + filename;
            FileStream fileStream = new FileStream(excelPath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(fileStream);
            fileStream.Close();










            return true;
        }

        //添加表头
        private static void AddHeader(DataTable dt, int rowIndex, IWorkbook workbook, ISheet sheet)
        {

            var row = sheet.CreateRow(rowIndex);

            var headFont = workbook.CreateFont();
            headFont.IsBold = true;

            //标题列样式
            var headStyle = workbook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center;
            headStyle.BorderBottom = BorderStyle.Thin;
            headStyle.BorderLeft = BorderStyle.Thin;
            headStyle.BorderRight = BorderStyle.Thin;
            headStyle.BorderTop = BorderStyle.Thin;
            headStyle.SetFont(headFont);

            for (int col = 0; col < dt.Columns.Count; col++)
            {
                var cell = row.CreateCell(col);
                cell.SetCellValue(dt.Columns[col].ColumnName);
                cell.CellStyle = headStyle;

            }
        }

        //添加表头
        private static void AddHeader(ArrayList HeadList, int rowIndex, IWorkbook workbook, ISheet sheet)
        {

            var row = sheet.CreateRow(rowIndex);

            var headFont = workbook.CreateFont();
            headFont.IsBold = true;

            //标题列样式
            var headStyle = workbook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center;
            headStyle.BorderBottom = BorderStyle.Thin;
            headStyle.BorderLeft = BorderStyle.Thin;
            headStyle.BorderRight = BorderStyle.Thin;
            headStyle.BorderTop = BorderStyle.Thin;
            headStyle.SetFont(headFont);

            for (int col = 0; col < HeadList.Count; col++)
            {
                var cell = row.CreateCell(col);
                cell.SetCellValue(HeadList[col].ToString());
                cell.CellStyle = headStyle;

            }
        }


    }
}
